"""This file handles database connection logic."""
from aifc import Error

import mysql.connector
from config.configs import Config

def get_connection():
    """
    获取MySQL数据库的连接对象。
    :return: MySQL连接对象
    """
    mydb = mysql.connector.connect(
        host=Config.MYSQL_HOST,
        user=Config.MYSQL_USER,
        password=Config.MYSQL_PASSWORD,
        database=Config.MYSQL_DATABASE,
        port=Config.MYSQL_PORT,
    )
    return mydb

def sql_execute(sql, val=None):
    mydb = get_connection()
    mycursor = mydb.cursor()
    try:
        if val:
            mycursor.execute(sql, val)
        else:
            mycursor.execute(sql)
        mydb.commit()
        print("SQL executed successfully")
    except mysql.connector.Error as err:
        print(f"Error: {err}")
        mydb.rollback()
    finally:
        mycursor.close()
        mydb.close()

# def sql_update(sql, val):
#     mydb = get_connection()
#     mycursor = mydb.cursor()
#     try:
#         mycursor.execute(sql, val)
#         mydb.commit()
#         print("Data updated successfully")
#     except mysql.connector.Error as err:
#         print(f"Error: {err}")
#     finally:
#         mycursor.close()
#         mydb.close()
# def sql_update(sql, val):
#     sql_execute(sql, val)
def sql_update(sql, val):
    mydb = None
    mycursor = None
    try:
        print(sql)
        print(val)
        mydb = get_connection()
        mycursor = mydb.cursor()
        mycursor.execute(sql, val)
        mydb.commit()
        print("Data updated successfully")
    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        if mycursor:
            mycursor.close()
        if mydb:
            mydb.close()


def sql_select(sql, val=None):
    mydb = get_connection()
    mycursor = mydb.cursor()
    if val:
        mycursor.execute(sql, val)
    else:
        mycursor.execute(sql)
    myresult = mycursor.fetchall()
    mycursor.close()
    mydb.close()
    return myresult

# 获取用户id
def get_last_row_id(mycursor):
    """
    获取最后插入行的ID。
    :param mycursor: MySQL游标对象
    :return: 最后插入行的ID
    """
    print(f'lastrowid:{mycursor.lastrowid}')
    return mycursor.lastrowid


def select_record(table_name, record_id):
    """查看指定表中指定 ID 的记录"""
    connection = get_connection()
    if connection is None:
        return "数据库连接失败"

    try:
        cursor = connection.cursor()
        sql = f'SELECT * FROM {table_name} WHERE id = %s'
        val = (record_id,)  # 注意这里是一个元组，即使只有一个值也要加上逗号
        print(f"查询表：{table_name}, 查询ID：{val}")

        cursor.execute(sql, val)

        # 读取查询结果
        result = cursor.fetchall()  # 获取所有的结果，如果只需要一条可以用 fetchone()

        if result:
            print(f"成功查看到 {len(result)} 条记录: {result}")
            return result  # 返回查询结果
        else:
            print("没有找到要查看的记录")
            return "没有找到要查看的记录"
    except Error as e:
        print(f"查看操作失败: {e}")
        return "查看操作失败"
    finally:
        cursor.close()  # 确保游标关闭
        connection.close()  # 确保连接关闭

# 根据指定的表删除指定的id记录
def delete_record(table_name, record_id):
    """删除指定表中指定 ID 的记录"""
    connection = get_connection()
    if connection is None:
        return "数据库连接失败"

    try:
        cursor = connection.cursor()
        sql = f'DELETE FROM {table_name} WHERE id = %s'
        val = (record_id,)  # 注意这里是一个元组，即使只有一个值也要加上逗号
        cursor.execute(sql, val)
        connection.commit()
        affected_rows = cursor.rowcount
        if affected_rows > 0:
            print(f"成功删除 {affected_rows} 条记录")
            return affected_rows
        else:
            print("没有找到要删除的记录")
            return "没有找到要删除的记录"
    except Error as e:
        print(f"删除操作失败: {e}")
        return "删除操作失败"
    finally:
        cursor.close()
        connection.close()


def insert_record(table_name, data):
    """向指定表中插入数据"""
    connection = get_connection()
    if connection is None:
        return "数据库连接失败"

    try:
        cursor = connection.cursor()
        print(f"需要新增的数据为 = \n{data}")
        # 获取字段名和值的列表，data 是一个字典，键是字段名，值是要插入的数据

        columns = ', '.join(data.keys())  # 字段名
        placeholders = ', '.join(['%s'] * len(data))  # 占位符
        values = tuple(data.values())  # 对应的值

        print(f"columns = {columns}")
        print(f"placeholders = {values}")
        # 构建 SQL 语句
        sql = f'INSERT INTO {table_name} ({columns}) VALUES ({placeholders})'

        # 执行 SQL 语句
        cursor.execute(sql, values)
        connection.commit()
        inserted_id = cursor.lastrowid  # 获取插入的 ID
        print(f"成功插入记录，ID 为: {inserted_id}")
        return inserted_id
    except Error as e:
        print(f"插入操作失败: {e}")
        return "插入操作失败"
    finally:
        cursor.close()
        connection.close()

# 自定义: insert_record()方法的测试
if __name__ == '__main__':
    data = {
    "name": "合肥中科院",
    "identity_card": 123456,
    "identity_card_sha": "123456",
	"admission_no": "987654321",
	"gender": "男",
	"age": "18",
	"fertility_history": "未婚",
	"education_level": "博士",
	"occupation": "算法工程师",
	"diagnosis": "没有毛病",
	"comorbidity": "针灸",
	"therapeutic_modality": "医保",
	"methods_of_medical_cost_coverage": "贷款",
	"marital_status": "未婚",
	"phone": "984264726482",
	"emergency_contact": "外星人",
	"emergency_contact_phone": "**************",
	"address": "**************",
	"agricultural_indicator": 1,
	"temperature": "100",
	"pulse": "100",
	"respiration_rate": "100",
	"blood_pressure": "100",
	"create_time": "2024-09-05 19:43:13",
	"update_time": "2024-09-05 19:43:13",
	"roles": "user"
    }
    table_name = 'users'  # 替换为你的表名
    insert_record(table_name, data)

# 通用的修改方法
def update_record(table_name, record_id, data):
    """修改指定表中指定 ID 的记录"""
    connection = get_connection()
    if connection is None:
        return "数据库连接失败"

    try:
        cursor = connection.cursor()

        # 动态生成 SQL 更新语句，data 是一个字典，键是字段名，值是要更新的内容
        columns = ', '.join([f"{key} = %s" for key in data.keys()])  # 构建 "column = %s" 的格式
        values = tuple(data.values())  # 对应的值
        values += (record_id,)  # 最后添加 record_id

        # 构建 SQL 语句
        sql = f'UPDATE {table_name} SET {columns} WHERE id = %s'

        # 执行 SQL 语句
        cursor.execute(sql, values)
        connection.commit()

        affected_rows = cursor.rowcount  # 获取影响的行数
        if affected_rows > 0:
            print(f"成功更新 {affected_rows} 条记录")
            return affected_rows
        else:
            print("没有找到要更新的记录")
            return "没有找到要更新的记录"
    except Error as e:
        print(f"更新操作失败: {e}")
        return "更新操作失败"
    finally:
        cursor.close()
        connection.close()
